import datetime
import time
from decimal import Decimal

import pymysql
from PySide6 import QtCore
from PySide6.QtCore import Qt
from PySide6.QtGui import QPalette
from PySide6.QtWidgets import QMainWindow, QTableWidget, QTableWidgetItem, QHeaderView, QAbstractItemView, QComboBox, \
    QCheckBox, QWidget, QHBoxLayout, QLabel, QToolButton, QMessageBox, QDialog, QListWidgetItem

from controllers.dialog_add_index import DialogAddIndex
from global_variables import GlobalVariables


class FieldListFillData(QMainWindow):

    def __init__(self, app):
        super().__init__()
        self.app = app
        self.ui = app.ui
        self.mysqldb = GlobalVariables.DATABASE_OBJ
        self.previous_row: int = -1
        self.previous_row_content_is_changed: bool = False
        self.field_list_combo_box_changed_list: list = []
        self.list_field_data = []
        self.list_index_data = []
        self.init()

    def init(self):
        self.define_signals()

    def define_signals(self):
        self.ui.add_column.clicked.connect(self.add_column_clicked)
        self.ui.del_column.clicked.connect(self.del_column_clicked)
        self.ui.refresh_column.clicked.connect(self.refresh_column_clicked)
        self.ui.add_index.clicked.connect(self.add_index_clicked)
        self.ui.del_index.clicked.connect(self.del_index_clicked)
        self.ui.refresh_index.clicked.connect(self.refresh_index_clicked)
        self.ui.field_list.cellChanged.connect(self.field_list_cell_changed)
        self.ui.field_list.cellDoubleClicked.connect(self.field_list_cell_double_clicked)
        self.ui.field_list.cellClicked.connect(self.field_list_cell_clicked)
        self.ui.move_up_column.clicked.connect(self.move_up_column_clicked)
        self.ui.move_down_column.clicked.connect(self.move_down_column_clicked)

    def field_list_fill_data_v2(self, obj: QTableWidget, columns: list, data: list):
        # 清空数据表
        obj.clearContents()
        obj.setRowCount(0)

        # 设置隔行变色
        obj.setAlternatingRowColors(True)
        obj.setPalette(QPalette(Qt.gray))

        # 设置列数
        obj.setColumnCount(len(columns))
        # 设置自适应宽度
        obj.horizontalHeader().setSectionResizeMode(QHeaderView.ResizeToContents)
        # 设置列最大宽度
        obj.horizontalHeader().setMaximumSectionSize(200)
        # 设置列最小宽度
        obj.horizontalHeader().setMinimumSectionSize(30)
        # 设置表格为整行选择
        obj.setSelectionBehavior(QAbstractItemView.SelectRows)

        map_k_column = {}
        for k in range(len(columns)):
            map_k_column[columns[k]] = k
            obj.setHorizontalHeaderItem(k, QTableWidgetItem(columns[k]))
            # obj.horizontalHeader().setSectionResizeMode(k, QHeaderView.Interactive)

        for d in data:
            row_count = obj.rowCount() + 1
            obj.setRowCount(row_count)
            for column in map_k_column:
                if isinstance(d[column], bool):
                    content = d[column]
                elif isinstance(d[column], str):
                    content = d[column]
                elif isinstance(d[column], int):
                    content = str(d[column])
                elif d[column] is None:
                    content = 'NULL'
                elif isinstance(d[column], bytes):
                    content = d[column].decode('utf-8')
                elif isinstance(d[column], Decimal):
                    content = str(d[column])
                elif isinstance(d[column], datetime.datetime):
                    content = d[column].strftime("%Y-%m-%d %H:%M:%S")
                else:
                    print(d[column])
                    raise Exception('column type is wrong')

                self.cell_widget(row_count - 1, map_k_column[column], obj, column, content)
        if obj.objectName() == 'field_list':
            self.ui.add_column.setDisabled(False)
            self.ui.del_column.setDisabled(True)
            self.ui.move_up_column.setDisabled(True)
            self.ui.move_down_column.setDisabled(True)
        elif obj.objectName() == 'index_list':
            self.ui.add_index.setDisabled(False)
            self.ui.del_index.setDisabled(True)

    def cell_widget(self, row: int, column: int, obj: QTableWidget, header: str, content: str):
        if obj.objectName() == 'field_list':
            item = self.cell_widget_field_list(
                row=row, column=column, header=header, content=content
            )
        elif obj.objectName() == 'index_list':
            item = self.cell_widget_index_list(
                row=row, column=column, header=header, content=content
            )
        else:
            item = QTableWidgetItem()

        if isinstance(item, QTableWidgetItem):
            obj.setItem(row, column, item)
        else:
            obj.setCellWidget(row, column, item)

    def cell_widget_index_list(self, row: int, column: int, header: str, content: str):
        cell_type = self.index_table_headers().get(header)
        if cell_type == 'string':
            item = QTableWidgetItem(content)
            if column == 0:
                table_name: str = self.ui.table_list.currentItem().text()
                item.table_name = table_name
        else:
            print(header, cell_type, content)
            raise Exception('index cell type is wrong')
        return item

    def cell_widget_field_list(self, row: int, column: int, header: str, content: str):
        cell_type = self.field_table_headers().get(header)
        if cell_type == 'string':
            item = QTableWidgetItem(content)
            if column == 0:
                item.old_field = content
                table_name: str = self.ui.table_list.currentItem().text()
                item.table_name = table_name
        elif cell_type == 'dict':
            box = QComboBox()
            box.adjustSize()
            parent = self.cell_add_widget(box)
            if column == 1:
                box.addItems(self.field_type())
                box.setProperty('editable', True)
                box.setDisabled(True)
            elif column == 6:
                box.addItems(self.column_key_type())
                box.setDisabled(True)
            elif column == 8:
                box.addItems(self.column_extra_type())
                box.setProperty('editable', True)
                box.setDisabled(True)
            else:
                box.addItems(self.field_type())

            if len(content) > 0:
                box.setCurrentText(content)
            box.currentTextChanged.connect(self.field_list_cell_combo_box_changed)
            box.setProperty('row', row)
            item = parent
        elif cell_type == 'bool':
            box = QCheckBox()
            parent = self.cell_add_widget(box)
            if content is True:
                box.setCheckState(Qt.Checked)
            else:
                box.setCheckState(Qt.Unchecked)
            box.setProperty('row', row)
            box.stateChanged.connect(self.field_list_cell_check_box_changed)
            item = parent
        else:
            print(header, cell_type, content)
            raise Exception('field cell type is wrong')
        return item

    def cell_add_widget(self, widget):
        parent = QWidget()
        widget.setParent(parent)
        layout = QHBoxLayout(parent)
        layout.setAlignment(Qt.AlignCenter)
        layout.setContentsMargins(0, 0, 0, 0)
        layout.addWidget(widget)
        return parent

    def field_list_clear(self):
        self.field_list_fill_data_v2(
            obj=self.ui.field_list,
            columns=[],
            data=[]
        )
        self.field_list_fill_data_v2(
            obj=self.ui.index_list,
            columns=[],
            data=[]
        )


    def fill_data(self, table_name: str):
        try:
            self.ui.field_list.cellChanged.disconnect(self.field_list_cell_changed)
        except Exception as e:
            pass
        self.field_list_fill_data(table_name)
        self.ui.field_list.cellChanged.connect(self.field_list_cell_changed)
        # 添加字段按钮与刷新按钮启用
        self.ui.add_column.setEnabled(True)
        self.ui.refresh_column.setEnabled(True)

        self.index_list_fill_data(table_name)
        # 添加索引按钮与刷新按钮启用
        self.ui.add_index.setEnabled(True)
        self.ui.refresh_index.setEnabled(True)

    def index_list_fill_data(self, table_name: str):
        # 获取当前数据库
        database = self.ui.database_select.currentText()

        # 数据表数据
        self.list_index_data = self.mysqldb.table_index(
            database=database,
            table_name=table_name
        )
        columns = []
        for col in self.list_index_data:
            columns.append({
                'Non_unique': col.get('Non_unique'),
                'Key_name': col.get('Key_name'),
                'Seq_in_index': col.get('Seq_in_index'),
                'Column_name': col.get('Column_name'),
                'Collation': col.get('Collation'),
                'Cardinality': col.get('Cardinality'),
                'Sub_part': col.get('Sub_part'),
                'Packed': col.get('Packed'),
                'Comment': col.get('Comment'),
            })

        self.field_list_fill_data_v2(
            obj=self.ui.index_list,
            columns=list(self.index_table_headers().keys()),
            data=columns
        )
        self.ui.index_list.currentCellChanged.connect(self.index_list_current_cell_changed)

    def field_list_fill_data(self, table_name: str):
        # 获取当前数据库
        database = self.ui.database_select.currentText()

        # 数据表数据
        self.list_field_data = self.mysqldb.table_columns(
            database=database,
            table_name=table_name
        )
        columns = []
        for col in self.list_field_data:
            length = col.get('COLUMN_TYPE', '')
            if length.count('(') > 0:
                length = length[length.index('(') + 1:length.index(')')]
            column = {
                'field': col.get('COLUMN_NAME'),
                'type': col.get('DATA_TYPE', '').upper(),
                'length': length,
                'unsigned': col.get('COLUMN_TYPE', '').count('unsigned') > 0,
                'zerofill': col.get('COLUMN_TYPE', '').count('zerofill') > 0,
                'nullable': col.get('IS_NULLABLE', '').count('YES') > 0,
                'key': col.get('COLUMN_KEY'),
                'default': col.get('COLUMN_DEFAULT'),
                'extra': col.get('EXTRA'),
                'encoding': col.get('CHARACTER_SET_NAME'),
                'collection': col.get('COLLATION_NAME'),
                'comment': col.get('COLUMN_COMMENT')
            }
            columns.append(column)

        self.field_list_fill_data_v2(
            obj=self.ui.field_list,
            columns=list(self.field_table_headers().keys()),
            data=columns
        )

        self.previous_row_content_is_changed = False
        self.previous_row = -1
        self.ui.field_list.currentCellChanged.connect(self.field_list_current_cell_changed)

    def index_table_headers(self):
        return {
            'Non_unique': 'string',
            'Key_name': 'string',
            'Seq_in_index': 'string',
            'Column_name': 'string',
            'Collation': 'string',
            'Cardinality': 'string',
            'Sub_part': 'string',
            'Packed': 'string',
            'Comment': 'string',
        }

    def field_table_headers(self):
        return {
            'field': 'string',
            'type': 'dict',
            'length': 'string',
            'unsigned': 'bool',
            'zerofill': 'bool',
            'nullable': 'bool',
            'key': 'dict',
            'default': 'string',
            'extra': 'dict',
            'encoding': 'string',
            'collection': 'string',
            'comment': 'string',
        }

    def column_extra_type(self):
        return [
            '',
            'auto_increment',
            'on update CURRENT_TIMESTAMP',
        ]

    def column_key_type(self):
        return [
            '',
            'PRI',
            'UNI',
            'MUL',
        ]

    def field_type(self):
        return [
            'TINYINT',
            'SMALLINT',
            'MEDIUMINT',
            'INT',
            'INTEGER',
            'BIGINT',
            'BIT',
            'REAL',
            'DOUBLE',
            'FLOAT',
            'DECIMAL',
            'CHAR',
            'VARCHAR',
            'TINYTEXT',
            'TEXT',
            'MEDIUMTEXT',
            'LONGTEXT',
            'DATE',
            'TIME',
            'YEAR',
            'DATETIME',
            'TIMESTAMP',
            'TINYBLOB',
            'BLOB',
            'MEDIUMBLOB',
            'LONGBLOB',
        ]

    def field_list_cell_clicked(self, row: int, column: int):
        self.ui.field_list.selectRow(row)

    def index_list_cell_clicked(self, row: int, column: int):
        self.ui.index_list.selectRow(row)

    def field_list_cell_double_clicked(self, row: int, column: int):
        widget: QWidget = self.ui.field_list.cellWidget(row, column)
        if not isinstance(widget, QWidget):
            return
        comboBox = widget.findChild(QComboBox)
        if not isinstance(comboBox, QComboBox):
            return
        if comboBox.property('editable'):
            comboBox.setEnabled(True)

    def index_list_current_cell_changed(self, current_row, current_column, previous_row, previous_column):
        self.ui.del_index.setDisabled(False)

    def field_list_current_cell_changed(self, current_row, current_column, previous_row, previous_column):
        # 只要位置发生变化，则已经选中行
        if self.sender().objectName() == 'field_list':
            self.ui.del_column.setDisabled(False)
            self.ui.move_up_column.setDisabled(False)
            self.ui.move_down_column.setDisabled(False)
        elif self.sender().objectName() == 'field_list':
            self.ui.del_index.setDisabled(False)
        # 如果 记录行 为-1，记录当前行，return
        if self.previous_row == current_row and current_column == 0:
            return
        if self.previous_row == -1:
            self.previous_row = current_row
            return
        # 如果当前行与记录行不一致，发起更新请求，更新行的行号为记录行;更新记录行为当前行
        if self.previous_row != current_row:
            self.row_cell_combox_set_disabled(self.previous_row)
            if self.previous_row_content_is_changed:
                # 更新上一行的数据
                self.field_update(self.previous_row)
                self.previous_row = current_row
                self.previous_row_content_is_changed = False
            else:
                # 如果行数一致
                self.previous_row = current_row

    def row_cell_combox_set_disabled(self, row: int):
        for column in range(0, self.ui.field_list.columnCount()):
            widget = self.ui.field_list.cellWidget(row, column)
            if not isinstance(widget, QWidget):
                continue
            comboBox = widget.findChild(QComboBox)
            if not isinstance(comboBox, QComboBox):
                continue
            if comboBox.isEnabled():
                comboBox.setDisabled(True)

    def field_list_cell_changed(self, row: int, column: int):
        self.previous_row = row
        self.previous_row_content_is_changed = True

    def field_list_cell_check_box_changed(self):
        box: QCheckBox = self.sender()
        self.ui.field_list: QTableWidget
        row = self.ui.field_list.rowAt(box.parent().y())
        self.field_update(row)

    def field_list_cell_combo_box_changed(self):
        box: QComboBox = self.sender()
        self.previous_row = box.property('row')
        self.previous_row_content_is_changed = True

    def field_update(self, row: int, after_field: str = ''):
        table: QTableWidget = self.ui.field_list
        if table.item(row, 0) is None:
            print('field_update is failed, row is ' + str(row))
            return
        if len(table.item(row, 0).text().strip()) == 0:
            return
        if getattr(table.item(row, 0), 'old_field') is None:
            old_field = ''
        else:
            old_field: str = table.item(row, 0).old_field
        field: str = table.item(row, 0).text().strip()
        field_type: str = table.cellWidget(row, 1).findChild(QComboBox).currentText()
        length: str = table.item(row, 2).text()
        is_unsigned: bool = table.cellWidget(row, 3).findChild(QCheckBox).isChecked()
        is_zerofull: bool = table.cellWidget(row, 4).findChild(QCheckBox).isChecked()
        is_nullable: bool = table.cellWidget(row, 5).findChild(QCheckBox).isChecked()
        key_text: str = table.cellWidget(row, 6).findChild(QComboBox).currentText()
        default: str = table.item(row, 7).text()
        extra: str = table.cellWidget(row, 8).findChild(QComboBox).currentText()
        encoding: str = table.item(row, 9).text()
        collection: str = table.item(row, 10).text()
        comment: str = table.item(row, 11).text()
        table_name: str = table.item(row, 0).table_name

        if len(old_field) == 0:
            sql = f'alter table `{table_name}` add column {field} '
        else:
            sql = f'alter table `{table_name}` change column {old_field} {field} '
        # todo 未来需要完善判断条件
        if len(length) > 0:
            if field_type.upper() in ["DATETIME"]:
                sql += f' {field_type} '
            else:
                sql += f' {field_type}({length}) '
        else:
            sql += f' {field_type} '

        if is_unsigned:
            sql += f' unsigned '

        if is_zerofull:
            sql += f' zerofill '
        if is_nullable:
            sql += f' null '
        else:
            sql += f' not null '

        if len(default) != 0:
            if str(default).upper() == 'NULL':
                if is_nullable:
                    sql += f' default {default} '
            elif field_type.upper() in ["DATETIME"] and str(default).upper() == 'CURRENT_TIMESTAMP':
                sql += f' default {default} '
            else:
                sql += f' default "{default}" '
        else:
            if field_type in ['VARCHAR', 'CHAR', 'TINYTEXT', 'TEXT', 'MEDIUMTEXT', 'LONGTEXT']:
                sql += f' default "" '

        sql += f' {extra} '
        sql += f' comment "{comment}" '

        if len(after_field) > 0:
            sql += f' after {after_field} '

        try:
            cursor = self.mysqldb.cursor_execute(sql)
            cursor.fetchall()
        except (pymysql.err.DataError, pymysql.err.OperationalError) as e:
            code, msg = e.args
            QMessageBox.critical(self, '修改字段错误', str(code) + "\t" + msg)

    def add_column_clicked(self):
        table_name = self.get_table_name()
        if table_name is None:
            return
        if self.ui.table_list.currentItem() is None:
            return
        database = self.ui.database_select.currentText()
        list_data = self.mysqldb.table_columns(
            database=database,
            table_name=table_name
        )
        row_count = self.ui.field_list.rowCount()
        if len(list_data) != row_count:
            # 如果第一行没有完成设置，不允许再次添加其他行
            return
        self.ui.field_list.setRowCount(row_count + 1)
        offset = 0
        for field, f_type in self.field_table_headers().items():
            if f_type == 'string':
                self.cell_widget(row_count, offset, self.ui.field_list, field, '')
            elif f_type == 'dict':
                self.cell_widget(row_count, offset, self.ui.field_list, field, '')
            elif f_type == 'bool':
                self.cell_widget(row_count, offset, self.ui.field_list, field, False)
            offset += 1

    def del_column_clicked(self):
        if len(self.ui.field_list.selectedItems()) != 0:
            self.ui.del_column.setDisabled(True)
        else:
            self.ui.del_column.setDisabled(False)

        listRow = set()
        for item in self.ui.field_list.selectedItems():
            if isinstance(item, QTableWidgetItem):
                listRow.add(item.row())

        if len(listRow) != 1:
            return
        row = listRow.pop()
        table: QTableWidget = self.ui.field_list
        if getattr(table.item(row, 0), 'old_field') is None:
            self.ui.field_list.removeRow(row)
            return

        old_field: str = table.item(row, 0).old_field
        table_name: str = table.item(row, 0).table_name
        # 删除逻辑，添加对话框，二次确认是否删除
        result = QMessageBox.question(self, '确认删除', f'是否确认删除字段 {old_field}')

        if result == QMessageBox.StandardButton.Yes:
            try:
                self.mysqldb.table_drop_column(table_name, old_field)
                self.refresh_column_clicked()
            except pymysql.err.OperationalError as e:
                code, msg = e.args
                QMessageBox.critical(self, '删除字段错误', str(code) + "\t" + msg)

    # 更新字段列表
    def refresh_column_clicked(self):
        self.ui.field_list: QTableWidget
        table_name: str = self.ui.table_list.currentItem().text()
        self.field_list_fill_data(table_name)

    # 更新索引列表
    def refresh_index_clicked(self):
        table_name: str = self.ui.table_list.currentItem().text()
        self.index_list_fill_data(table_name)

    # 删除索引
    def del_index_clicked(self):
        if len(self.ui.index_list.selectedItems()) != 0:
            self.ui.del_index.setDisabled(True)
        else:
            self.ui.del_index.setDisabled(False)

        listRow = set()
        for item in self.ui.index_list.selectedItems():
            if isinstance(item, QTableWidgetItem):
                listRow.add(item.row())

        if len(listRow) != 1:
            return
        row = listRow.pop()
        table: QTableWidget = self.ui.index_list
        table_name: str = table.item(row, 0).table_name
        index_name: str = table.item(row, 1).text()
        result = QMessageBox.question(self, '确认删除', f'是否确认删除索引 {index_name}')
        if result == QMessageBox.StandardButton.Yes:
            try:
                self.mysqldb.table_drop_index(table_name, index_name)
                self.refresh_column_clicked()
            except pymysql.err.OperationalError as e:
                code, msg = e.args
                QMessageBox.critical(self, '删除索引错误', str(code) + "\t" + msg)

        self.refresh_index_clicked()

    def add_index_clicked(self):
        dialog = DialogAddIndex(self.ui.index_list, self.app)
        # dialog显示后，主控件不可点击
        dialog.setWindowModality(Qt.ApplicationModal)
        dialog.show()

    # 表格字段向上移动一行
    def move_up_column_clicked(self):
        # 获取当前行
        if len(self.ui.field_list.selectedItems()) == 0:
            return

        listRow = set()
        for item in self.ui.field_list.selectedItems():
            if isinstance(item, QTableWidgetItem):
                listRow.add(item.row())
        # 如果选中多行，则不作处理
        if len(listRow) != 1:
            return

        row = listRow.pop()
        # 获取当前行字段
        self.ui.field_list: QTableWidget
        after_field = self.ui.field_list.item(row, 0).text().strip()
        self.field_update(row - 1, after_field=after_field)
        self.refresh_column_clicked()
        self.ui.field_list.selectRow(row - 1)

    # 表格字段向下移动一行
    def move_down_column_clicked(self):
        # 获取当前行
        if len(self.ui.field_list.selectedItems()) == 0:
            return

        listRow = set()
        for item in self.ui.field_list.selectedItems():
            if isinstance(item, QTableWidgetItem):
                listRow.add(item.row())
        # 如果选中多行，则不作处理
        if len(listRow) != 1:
            return

        row = listRow.pop()
        # 当前行向下移动一行
        # 获取下一行字段
        # 更新当前字段，为after {{下一行字段}}
        self.ui.field_list: QTableWidget
        after_field = self.ui.field_list.item(row + 1, 0).text().strip()
        self.field_update(row, after_field=after_field)
        self.refresh_column_clicked()
        self.ui.field_list.selectRow(row + 1)

    def get_table_name(self):
        item = self.ui.table_list.currentItem()
        if isinstance(item, QListWidgetItem):
            return item.text()
        return None
